package dao;

import model.User;
import util.*;

import java.lang.reflect.InvocationTargetException;
import java.math.BigInteger;
import java.sql.SQLException;
import java.util.List;

public class UsersManager implements UsersManagement{

    public enum UserDAOOptionResult {DUP_UNIKE, DUP_UNAME, DUP_UID, FAIL_UNKNOW, SUCCEED}

    @Override
    public UserDAOOptionResult register(User u) {
        //要添加的列String
        StringBuilder sql_columns = new StringBuilder("");
        //values子句里的?
        StringBuilder sql_args    = new StringBuilder("");
        //实参列表
        ArgsList<Object> args   = new ArgsList<Object>();

        //用户名(非空)
        if(Common.isStringEmpty(u.getU_name())) return null;
        else {
            sql_columns.append("u_name");
            sql_args.append("?");
            args.add(u.getU_name());
        }

        //密码(非空)
        if(Common.isStringEmpty(u.getU_passwd())) return null;
        else {
            sql_columns.append(", u_passwd");
            sql_args.append(", ?");
            args.add(u.getU_passwd());
        }

        //昵称(非空)
        if(Common.isStringEmpty(u.getU_nick())) return null;
        else {
            sql_columns.append(", u_nick");
            sql_args.append(", ?");
            args.add(u.getU_nick());
        }

        //电话
        if(Common.isStringEmpty(u.getU_tel())) {
            sql_columns.append(", u_tel");
            sql_args.append(", ?");
            args.add(u.getU_tel());
        }

        //状态(非空)
        if(Common.isStringEmpty(u.getU_state())) return null;
        else {
            sql_columns.append(", u_state");
            sql_args.append(", ?");
            args.add(u.getU_state());
        }

        //VIP截止日期
        if(u.getU_vip_ddate() == null) {
            //默认值
            sql_columns.append(", u_vip_ddate");
            sql_args.append(", CURRENT_TIMESTAMP");
        } else {
            sql_columns.append(", u_vip_ddate");
            sql_args.append(", ?");
            args.add(u.getU_vip_ddate());
        }

        //积分
        if(u.getU_score() == null) {
            //默认值
            sql_columns.append(", u_score");
            sql_args.append(", 0");
        } else {
            sql_columns.append(", u_score");
            sql_args.append(", ?");
            args.add(u.getU_score());
        }

        //钱包
        if(u.getU_bank() == null) {
            //默认值
            sql_columns.append(", u_bank");
            sql_args.append(", 0");
        } else {
            sql_columns.append(", u_bank");
            sql_args.append(", ?");
            args.add(u.getU_bank());
        }

        //头像
        if(Common.isStringEmpty(u.getU_pic_address())) {
            sql_columns.append(", u_pic_address");
            sql_args.append(", ?");
            args.add(u.getU_pic_address());
        }

        String sql = "insert into t_users(" + sql_columns.toString() + ") values(" + sql_args.toString() + ")";
        try {
            //从连接池获取一个连接
            JDBCConnection conn = JDBCFactory.takeJDBC();
            //Object o = JDBCConnection.getGeneratedKey(conn.updateForGeneratedKeys(args, sql));
            int count = conn.update(args, sql);
            //归还连接
            JDBCFactory.returnBack(conn);
            if(count == 0) return UserDAOOptionResult.FAIL_UNKNOW; else return UserDAOOptionResult.SUCCEED;
            //Long gKey = (o == null ? null : ((BigInteger) o).longValue());
            //return gKey;
        } catch(SQLException e) {
            System.err.println(e.getMessage());
            switch(e.getErrorCode()) {
                case 1062:
                    if(e.getMessage().toLowerCase().contains("'u_nick'")) {
                        return UserDAOOptionResult.DUP_UNIKE;
                    } else if(e.getMessage().toLowerCase().contains("'u_name'")) {
                        return UserDAOOptionResult.DUP_UNAME;
                    }
                    break;
            }
            return UserDAOOptionResult.FAIL_UNKNOW;
        }
    }

    @Override
    public Long delete(User u) {
        return null;
    }

    @Override
    public Long alter(User u, User after, Boolean increase) {

        if(u == null  ||  after == null  ||  after.isEmpty()) return null;

        //待拼接的sql条件参数字符串
        StringBuilder sql_condition       = new StringBuilder("");
        //待拼接的sql更新设置值参数字符串
        StringBuilder sql_sets            = new StringBuilder("");
        //条件实参列表
        ArgsList<Object> args_condition = new ArgsList<Object>();
        //更新实参列表
        ArgsList<Object> args_sets      = new ArgsList<Object>();

        //根据用户id或用户名或用户id和用户名的组合进行条件过滤
        if(u.getU_id() != null) {
            sql_condition.append(" u_id = ?");
            args_condition.add(u.getU_id());
        }
        if(u.getU_name() != null) {
            if(u.getU_id() != null) sql_condition.append(" and");
            sql_condition.append(" u_name = ?");
            args_condition.add(u.getU_name());
        }
        else if(u.getU_id() == null) return null;
        //-------------------------------------------------------------

        //第一个参数flag
        boolean firstArg = true;
        if(!Common.isStringEmpty(after.getU_name()))
        {
            //如果不是第一个参数，先拼接一个逗号到sql
            if(!firstArg) sql_sets.append(',');
            //拼接参数到sql
            sql_sets.append(" u_name = ?");
            //添加实参
            args_sets.add(after.getU_name());
            firstArg = false;
        }

        if(!Common.isStringEmpty(after.getU_passwd()))
        {
            if(!firstArg) sql_sets.append(',');
            sql_sets.append(" u_passwd = ?");
            args_sets.add(after.getU_passwd());
            firstArg = false;
        }

        if(!Common.isStringEmpty(after.getU_nick()))
        {
            if(!firstArg) sql_sets.append(',');
            sql_sets.append(" u_nick = ?");
            args_sets.add(after.getU_nick());
            firstArg = false;
        }

        if(!Common.isStringEmpty(after.getU_tel()))
        {
            if(!firstArg) sql_sets.append(',');
            sql_sets.append(" u_tel = ?");
            args_sets.add(after.getU_tel());
            firstArg = false;
        }

        if(!Common.isStringEmpty(after.getU_state()))
        {
            if(!firstArg) sql_sets.append(',');
            sql_sets.append(" u_state = ?");
            args_sets.add(after.getU_state());
            firstArg = false;
        }

        if(after.getU_vip_ddate() != null)
        {
            if(!firstArg) sql_sets.append(',');
            sql_sets.append(" u_vip_ddate = ?");
            args_sets.add(after.getU_vip_ddate());
            firstArg = false;
        }

        if(after.getU_score() != null)
        {
            if(!firstArg) sql_sets.append(',');
            sql_sets.append(" u_score = ?");
            args_sets.add(after.getU_score());
            firstArg = false;
        }

        if(after.getU_bank() != null)
        {
            if(!firstArg) sql_sets.append(',');
            if(increase != null) {
                if(increase){
                    sql_sets.append(" u_bank = u_bank + ?");
                } else {
                    sql_sets.append(" u_bank = u_bank - ?");
                }
            } else sql_sets.append(" u_bank = ?");
            args_sets.add(after.getU_bank());
            firstArg = false;
        }

        if(!Common.isStringEmpty(after.getU_pic_address()))
        {
            if(!firstArg) sql_sets.append(',');
            sql_sets.append(" u_pic_address = ?");
            args_sets.add(after.getU_pic_address());
            firstArg = false;
        }

        if(!Common.isStringEmpty(after.getU_recv_address()))
        {
            if(!firstArg) sql_sets.append(',');
            sql_sets.append(" u_recv_address = ?");
            args_sets.add(after.getU_recv_address());
        }

        //将条件实参添加到设置实参后部(where 在 set 子句后)
        args_sets.addAll(args_condition);

        String sql = "update t_users set" + sql_sets.toString() + " where" + sql_condition.toString();
        //System.out.println(sql);
        try {
            //从连接池获取一个连接
            JDBCConnection conn = JDBCFactory.takeJDBC();
            Object o = JDBCConnection.getGeneratedKey(conn.updateForGeneratedKeys(args_sets, sql));
            //归还连接
            JDBCFactory.returnBack(conn);
            Long gKey = (o == null ? null : ((BigInteger) o).longValue());
            return gKey;
        } catch(SQLException e) {
            System.err.println(e.getMessage());
            return null;
        }
    }

    public Long alter(User u, User after) {return alter(u, after, null);}

    @Override
    public List<User> query(User u, int pageSize, int page) {

        String sql = "select * from t_users where 1 = 1";
        StringBuilder sql_condition = new StringBuilder();
        ArgsList<Object> args = new ArgsList<>();

        //!Common.isStringEmpty()
        if(u.getU_id() != null) {
            sql_condition.append(" and u_id = ?");
            args.add(u.getU_id());
        }

        if(!Common.isStringEmpty(u.getU_name())) {
            sql_condition.append(" and u_name = ?");
            args.add(u.getU_name());
        }

        if(!Common.isStringEmpty(u.getU_nick())) {
            sql_condition.append(" and u_nick = ?");
            args.add(u.getU_nick());
        }

        if(!Common.isStringEmpty(u.getU_tel())) {
            sql_condition.append(" and u_tel = ?");
            args.add(u.getU_tel());
        }

        if(!Common.isStringEmpty(u.getU_state())) {
            sql_condition.append(" and u_state = ?");
            args.add(u.getU_state());
        }

        if(u.getU_vip_ddate() != null) {
            sql_condition.append(" and u_vip_ddate = ?");
            args.add(u.getU_vip_ddate());
        }

        if(u.getU_score() != null) {
            sql_condition.append(" and u_score = ?");
            args.add(u.getU_score());
        }

        if(u.getU_bank() != null) {
            sql_condition.append(" and u_bank = ?");
            args.add(u.getU_bank());
        }

        if(!Common.isStringEmpty(u.getU_pic_address())) {
            sql_condition.append(" and u_pic_address = ?");
            args.add(u.getU_pic_address());
        }

        sql += sql_condition.toString() + " limit ?, ?";
        args.add((page - 1) * pageSize);
        args.add(pageSize);

        List<User> result = null;
        try {
            JDBCConnection conn = JDBCFactory.takeJDBC();
            if(conn != null) result = conn.queryEntities(User.class, args, sql);
            JDBCFactory.returnBack(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            return new ResultList<>();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
            return new ResultList<>();
        }

        return result;
    }

    @Override
    public List<User> query(User u) {
        String sql = "select * from t_users where 1 = 1";
        StringBuilder sql_condition = new StringBuilder();
        ArgsList<Object> args = new ArgsList<Object>();

        if(u.getU_id() != null) {
            sql_condition.append(" and u_id = ?");
            args.add(u.getU_id());
        }

        if(!Common.isStringEmpty(u.getU_name())) {
            sql_condition.append(" and u_name = ?");
            args.add(u.getU_name());
        }

        if(!Common.isStringEmpty(u.getU_nick())) {
            sql_condition.append(" and u_nick = ?");
            args.add(u.getU_nick());
        }

        if(!Common.isStringEmpty(u.getU_tel())) {
            sql_condition.append(" and u_tel = ?");
            args.add(u.getU_tel());
        }

        if(!Common.isStringEmpty(u.getU_state())) {
            sql_condition.append(" and u_state = ?");
            args.add(u.getU_state());
        }

        if(u.getU_vip_ddate() != null) {
            sql_condition.append(" and u_vip_ddate = ?");
            args.add(u.getU_vip_ddate());
        }

        if(u.getU_score() != null) {
            sql_condition.append(" and u_score = ?");
            args.add(u.getU_score());
        }

        if(u.getU_bank() != null) {
            sql_condition.append(" and u_bank = ?");
            args.add(u.getU_bank());
        }

        if(!Common.isStringEmpty(u.getU_pic_address())) {
            sql_condition.append(" and u_pic_address = ?");
            args.add(u.getU_pic_address());
        }

        List<User> result = new ResultList<User>();
        try {
            JDBCConnection conn = JDBCFactory.takeJDBC();
            List<Object[]> rs = conn.query(args, sql + sql_condition);
            JDBCFactory.returnBack(conn);

            for (Object[] row : rs) result.add(new User(row));
            return result;
        } catch(SQLException e) {
            System.err.println(e.getMessage());
            return null;
        }
    }

    @Override
    public List<Object[]> queryMessages(User u, boolean unreadOnly) {
        return null;
    }

    //测试用main方法
    public static void main(String[] args) {

        //User u = new User();
       //u.setU_name("testZhang998");
        /*u.setU_passwd("cszs");
        u.setU_nick("测试张11");
        u.setU_tel("12310123456");
        u.setU_state("启用");
        u.setU_score(0L);*/
/*
        User u1 = new User();
        u1.setU_name("testZhang988");
        u1.setU_nick("测试张九88");
        u1.setU_tel("31612");
        u1.setU_state("启用");
        u1.setU_bank(new BigDecimal("200.00"));*/
        //System.out.println(new UsersManager().register(u));
       // new UsersManager().alter(u, u1);
        for(User u : new UsersManager().query(new User()))
        {
            System.out.println(u);
        }
    }
}
